from django.db import models
from django.db.models import Count, Sum, Avg, Max, F, ExpressionWrapper, FloatField, Case, When, Value, IntegerField
from django.utils import timezone
from django.http import JsonResponse
from rest_framework.decorators import api_view, permission_classes
from rest_framework.permissions import IsAuthenticated
from rest_framework.response import Response
from datetime import timedelta, datetime
from apps.OMM.models import Customer, Order
import logging

def calculate_rfm_scores(customers_data):
    """
    计算RFM分数
    R (Recency): 最近购买时间 (天数越少分数越高)
    F (Frequency): 购买频率 (购买次数越多分数越高)
    M (Monetary): 消费金额 (金额越高分数越高)
    """
    # 如果没有数据，返回空列表
    if not customers_data:
        return []
    
    # 排序数据用于计算分位数
    r_values = sorted([c['days_since_last_purchase'] for c in customers_data])
    f_values = sorted([c['purchase_count'] for c in customers_data])
    m_values = sorted([c['total_amount'] for c in customers_data])
    
    # 计算三分位数阈值
    r_tertiles = [
        r_values[len(r_values) // 3],
        r_values[2 * len(r_values) // 3]
    ]
    f_tertiles = [
        f_values[len(f_values) // 3],
        f_values[2 * len(f_values) // 3]
    ]
    m_tertiles = [
        m_values[len(m_values) // 3],
        m_values[2 * len(m_values) // 3]
    ]
    
    # 为每个客户分配RFM分数
    for customer in customers_data:
        # R分数 (最近购买越近，分数越高)
        if customer['days_since_last_purchase'] <= r_tertiles[0]:
            customer['r_score'] = 3
        elif customer['days_since_last_purchase'] <= r_tertiles[1]:
            customer['r_score'] = 2
        else:
            customer['r_score'] = 1
            
        # F分数 (购买频率越高，分数越高)
        if customer['purchase_count'] >= f_tertiles[1]:
            customer['f_score'] = 3
        elif customer['purchase_count'] >= f_tertiles[0]:
            customer['f_score'] = 2
        else:
            customer['f_score'] = 1
            
        # M分数 (消费金额越高，分数越高)
        if customer['total_amount'] >= m_tertiles[1]:
            customer['m_score'] = 3
        elif customer['total_amount'] >= m_tertiles[0]:
            customer['m_score'] = 2
        else:
            customer['m_score'] = 1
            
        # 计算RFM总分
        customer['rfm_score'] = customer['r_score'] + customer['f_score'] + customer['m_score']
        
        # 确定客户类型和客户价值
        determine_customer_type(customer)
        
    return customers_data

def determine_customer_type(customer):
    """根据RFM分数确定客户类型和价值"""
    r = customer['r_score']
    f = customer['f_score']
    m = customer['m_score']
    
    # 客户类型
    if r == 3 and f == 3 and m == 3:
        customer['customer_type'] = '重要价值客户'
    elif r == 3 and f >= 2 and m >= 2:
        customer['customer_type'] = '重要保持客户'
    elif r <= 2 and f >= 2 and m >= 2:
        customer['customer_type'] = '重要发展客户'
    elif r == 1 and f >= 2 and m >= 1:
        customer['customer_type'] = '重要挽留客户'
    elif r >= 2 and f >= 1 and m >= 2:
        customer['customer_type'] = '一般价值客户'
    elif r >= 2 and f == 1 and m >= 1:
        customer['customer_type'] = '一般保持客户'
    elif r >= 1 and f >= 2 and m == 1:
        customer['customer_type'] = '一般发展客户'
    else:
        customer['customer_type'] = '一般挽留客户'
    
    # 客户价值
    if r >= 2 and f >= 2 and m >= 2:
        customer['customer_value'] = '高价值'
    elif r == 1 and f >= 2 and m >= 2:
        customer['customer_value'] = '流失风险'
    elif (r >= 2 and f >= 2 and m == 1) or (r >= 2 and f == 1 and m >= 2):
        customer['customer_value'] = '中价值'
    else:
        customer['customer_value'] = '低价值'

@api_view(['GET'])
# @permission_classes([IsAuthenticated])  # 暂时注释掉权限检查以排除认证问题
def get_rfm_summary(request):
    """获取RFM分析的汇总数据"""
    # 添加日志记录
    logger = logging.getLogger(__name__)
    logger.info("RFM概览API被调用")
    
    today = timezone.now().date()
    thirty_days_ago = today - timedelta(days=30)
    last_month = today - timedelta(days=60)
    
    # 查询客户总数
    total_customers = Customer.objects.count()
    
    # 查询近30天新增客户数
    new_customers_30days = Order.objects.filter(
        order_date__gte=thirty_days_ago
    ).values('customer_name').distinct().count()
    
    # 计算客户平均价值
    avg_customer_value = Order.objects.aggregate(
        avg_value=Avg('order_amount')
    )['avg_value'] or 0
    
    # 计算上个月同期客户总数，用于计算增长率
    last_month_customers = Customer.objects.filter(
        id__in=Order.objects.filter(
            order_date__lt=thirty_days_ago
        ).values('customer_name')
    ).count()
    
    # 计算上个月同期新增客户数
    last_month_new_customers = Order.objects.filter(
        order_date__gte=last_month,
        order_date__lt=thirty_days_ago
    ).values('customer_name').distinct().count()
    
    # 计算上个月平均客户价值
    last_month_avg_value = Order.objects.filter(
        order_date__lt=thirty_days_ago
    ).aggregate(
        avg_value=Avg('order_amount')
    )['avg_value'] or 0
    
    # 计算客户复购率 (有多次购买记录的客户占比)
    customers_with_orders = Order.objects.values(
        'customer_name'
    ).annotate(
        order_count=Count('id')
    )
    
    repeat_customers = customers_with_orders.filter(order_count__gt=1).count()
    total_customers_with_orders = customers_with_orders.count()
    
    repeat_purchase_rate = (repeat_customers / total_customers_with_orders * 100) if total_customers_with_orders > 0 else 0
    
    # 获取上个月的复购率数据
    last_month_customers_with_orders = Order.objects.filter(
        order_date__lt=thirty_days_ago
    ).values('customer_name').annotate(
        order_count=Count('id')
    )
    
    last_month_repeat_customers = last_month_customers_with_orders.filter(
        order_count__gt=1
    ).count()
    
    last_month_total_customers_with_orders = last_month_customers_with_orders.count()
    
    last_month_repeat_rate = (
        last_month_repeat_customers / last_month_total_customers_with_orders * 100
    ) if last_month_total_customers_with_orders > 0 else 0
    
    # 计算增长率
    customer_growth = ((total_customers - last_month_customers) / last_month_customers * 100) if last_month_customers > 0 else 0
    new_customer_growth = ((new_customers_30days - last_month_new_customers) / last_month_new_customers * 100) if last_month_new_customers > 0 else 0
    avg_value_growth = ((avg_customer_value - last_month_avg_value) / last_month_avg_value * 100) if last_month_avg_value > 0 else 0
    repeat_rate_growth = (repeat_purchase_rate - last_month_repeat_rate) if last_month_repeat_rate > 0 else 0
    
    return Response({
        'total_customers': total_customers,
        'new_customers_30days': new_customers_30days,
        'avg_customer_value': round(avg_customer_value, 2),
        'repeat_purchase_rate': round(repeat_purchase_rate, 2),
        'customer_growth': round(customer_growth, 2),
        'new_customer_growth': round(new_customer_growth, 2),
        'avg_value_growth': round(avg_value_growth, 2),
        'repeat_rate_growth': round(repeat_rate_growth, 2)
    })

@api_view(['GET'])
# @permission_classes([IsAuthenticated])  # 暂时注释掉权限检查以排除认证问题
def get_rfm_matrix(request):
    """获取客户价值矩阵数据"""
    # 添加日志记录
    logger = logging.getLogger(__name__)
    logger.info("RFM矩阵API被调用")
    
    # 获取所有客户及其RFM数据
    customers_data = get_customers_rfm_data()
    
    # 计算RFM分数
    customers_with_scores = calculate_rfm_scores(customers_data)
    
    # 价值矩阵数据 (F 和 M 的组合)
    matrix_data = [
        [0, 0, 0], [0, 0, 0], [0, 0, 0],  # 初始化3x3矩阵
        [0, 0, 0], [0, 0, 0], [0, 0, 0],
        [0, 0, 0], [0, 0, 0], [0, 0, 0]
    ]
    
    # 填充矩阵数据
    for customer in customers_with_scores:
        f_score = customer['f_score'] - 1  # 转为0-based索引
        m_score = customer['m_score'] - 1  # 转为0-based索引
        
        # 更新矩阵计数
        idx = f_score * 3 + m_score
        matrix_data[idx][0] = f_score
        matrix_data[idx][1] = 2 - m_score  # 反转M轴以符合图表要求
        matrix_data[idx][2] = matrix_data[idx][2] + 1  # 增加计数
    
    # 返回矩阵数据
    return Response(matrix_data)

@api_view(['GET'])
# @permission_classes([IsAuthenticated])  # 暂时注释掉权限检查以排除认证问题
def get_customer_types(request):
    """获取客户类型分布数据"""
    # 添加日志记录
    logger = logging.getLogger(__name__)
    logger.info("客户类型分布API被调用")
    
    try:
        # 获取所有客户及其RFM数据
        customers_data = get_customers_rfm_data()
        
        # 计算RFM分数
        customers_with_scores = calculate_rfm_scores(customers_data)
        
        # 统计各类型客户数量
        customer_types = {
            '重要价值客户': 0,
            '重要保持客户': 0,
            '重要发展客户': 0,
            '重要挽留客户': 0,
            '一般价值客户': 0,
            '一般保持客户': 0,
            '一般发展客户': 0,
            '一般挽留客户': 0
        }
        
        for customer in customers_with_scores:
            customer_type = customer['customer_type']
            customer_types[customer_type] += 1
        
        # 转换为饼图所需格式
        pie_data = []
        colors = {
            '重要价值客户': '#00ffb3',
            '重要保持客户': '#00c9ff',
            '重要发展客户': '#ffb800',
            '重要挽留客户': '#ff6b6b',
            '一般价值客户': '#3a36db',
            '一般保持客户': '#5653ea',
            '一般发展客户': '#8884ff',
            '一般挽留客户': '#b3b0ff'
        }
        
        for type_name, count in customer_types.items():
            if count > 0:
                pie_data.append({
                    'name': type_name,
                    'value': count,
                    'itemStyle': {'color': colors.get(type_name, '#cccccc')}
                })
        
        # 确保有数据返回，即使没有客户
        if not pie_data:
            for type_name, color in colors.items():
                pie_data.append({
                    'name': type_name,
                    'value': 0,
                    'itemStyle': {'color': color}
                })
        
        logger.info(f"客户类型分布API返回 {len(pie_data)} 条数据")
        return Response(pie_data)
    
    except Exception as e:
        logger.error(f"客户类型分布API出错: {str(e)}")
        # 返回空数组而不是抛出错误
        return Response([])

@api_view(['GET'])
# @permission_classes([IsAuthenticated])  # 暂时注释掉权限检查以排除认证问题
def get_rfm_analysis_results(request):
    """获取RFM分析结果列表"""
    # 添加日志记录
    logger = logging.getLogger(__name__)
    logger.info("RFM分析结果API被调用，参数：" + str(request.GET))
    
    page = int(request.GET.get('page', 1))
    page_size = int(request.GET.get('page_size', 10))
    search_query = request.GET.get('search', '')
    
    # 获取所有客户及其RFM数据
    customers_data = get_customers_rfm_data()
    
    # 计算RFM分数
    customers_with_scores = calculate_rfm_scores(customers_data)
    
    # 搜索过滤
    if search_query:
        customers_with_scores = [
            c for c in customers_with_scores 
            if search_query.lower() in c['customer_name'].lower()
        ]
    
    # 分页
    total_items = len(customers_with_scores)
    total_pages = (total_items + page_size - 1) // page_size
    
    start_idx = (page - 1) * page_size
    end_idx = min(start_idx + page_size, total_items)
    
    paginated_results = customers_with_scores[start_idx:end_idx]
    
    return Response({
        'results': paginated_results,
        'pagination': {
            'total': total_items,
            'page': page,
            'page_size': page_size,
            'total_pages': total_pages
        }
    })

def get_customers_rfm_data():
    """获取所有客户的RFM基础数据"""
    today = timezone.now().date()
    
    # 添加日志记录
    logger = logging.getLogger(__name__)
    logger.info("开始获取客户RFM数据")
    
    # 查询所有有订单的客户
    customers_with_orders = Order.objects.values(
        'customer_name'
    ).annotate(
        customer_id=F('customer_name__id'),
        customer_name_text=F('customer_name__customer_name')
    ).distinct()
    
    # 记录查询到的客户数量
    logger.info(f"查询到 {customers_with_orders.count()} 个有订单的客户")
    
    customers_data = []
    
    for customer in customers_with_orders:
        customer_name_id = customer['customer_name']
        
        # 获取该客户的最后购买日期
        last_purchase = Order.objects.filter(
            customer_name_id=customer_name_id
        ).aggregate(
            last_date=Max('order_date')
        )['last_date']
        
        # 计算距今天数
        days_since_last_purchase = (today - last_purchase).days if last_purchase else 999
        
        # 获取购买次数
        purchase_count = Order.objects.filter(
            customer_name_id=customer_name_id
        ).count()
        
        # 获取总消费金额
        total_amount = Order.objects.filter(
            customer_name_id=customer_name_id
        ).aggregate(
            total=Sum('order_amount')
        )['total'] or 0
        
        customers_data.append({
            'customer_id': customer['customer_id'],
            'customer_name': customer['customer_name_text'],
            'days_since_last_purchase': days_since_last_purchase,
            'purchase_count': purchase_count,
            'total_amount': float(total_amount)
        })
    
    # 记录处理后的客户数据数量
    logger.info(f"处理完成，共生成 {len(customers_data)} 条客户RFM数据")
    
    return customers_data

@api_view(['GET'])
# @permission_classes([IsAuthenticated])  # 暂时注释掉权限检查以排除认证问题
def get_rfm_trends(request):
    """获取RFM趋势数据（最近6个月）"""
    logger = logging.getLogger(__name__)
    logger.info("RFM趋势API被调用")
    
    try:
        today = timezone.now().date()
        
        # 计算最近6个月的日期范围
        months_data = []
        for i in range(5, -1, -1):  # 从5到0，共6个月
            month_start = today.replace(day=1) - timedelta(days=30*i)
            if i > 0:
                month_end = today.replace(day=1) - timedelta(days=30*(i-1) - 1)
            else:
                month_end = today
                
            month_name = month_start.strftime("%Y-%m")
            
            # 获取该月的RFM平均分数
            month_orders = Order.objects.filter(
                order_date__gte=month_start,
                order_date__lte=month_end
            )
            
            # 获取该月购买的客户及次数
            customers_with_frequency = month_orders.values(
                'customer_name'
            ).annotate(
                purchase_count=Count('id'),
                total_amount=Sum('order_amount')
            )
            
            # 计算R、F、M的平均值
            total_customers = customers_with_frequency.count()
            
            if total_customers > 0:
                avg_recency = (today - month_end).days  # 平均近度（最近一次购买距今的天数）
                avg_frequency = sum(c['purchase_count'] for c in customers_with_frequency) / total_customers
                avg_monetary = sum(c['total_amount'] for c in customers_with_frequency) / total_customers
                
                # 将R转换为1-5分，越近得分越高
                r_score = max(1, min(5, 6 - avg_recency // 30))  # 每30天为一个等级
            else:
                avg_recency = 0
                avg_frequency = 0
                avg_monetary = 0
                r_score = 0
                
            months_data.append({
                'month': month_name,
                'r_score': r_score,
                'f_score': min(5, avg_frequency),  # 限制在1-5分
                'm_score': min(5, avg_monetary / 1000)  # 按每1000元为一个等级，限制在1-5分
            })
        
        logger.info(f"RFM趋势API返回 {len(months_data)} 个月的数据")
        return Response(months_data)
    except Exception as e:
        logger.error(f"RFM趋势API出错: {str(e)}")
        return Response([])

@api_view(['GET'])
# @permission_classes([IsAuthenticated])  # 暂时注释掉权限检查以排除认证问题
def get_customer_lifecycle(request):
    """获取客户生命周期阶段分析数据"""
    logger = logging.getLogger(__name__)
    logger.info("客户生命周期API被调用")
    
    try:
        # 获取所有客户RFM数据
        customers_data = get_customers_rfm_data()
        customers_with_scores = calculate_rfm_scores(customers_data)
        
        # 定义生命周期阶段
        lifecycle_stages = {
            '新客户': 0,
            '成长客户': 0,
            '活跃客户': 0,
            '忠诚客户': 0,
            '沉睡客户': 0,
            '流失风险': 0,
            '已流失': 0
        }
        
        # 根据RFM分数划分生命周期阶段
        for customer in customers_with_scores:
            r = customer['r_score']
            f = customer['f_score']
            m = customer['m_score']
            days = customer['days_since_last_purchase']
            frequency = customer['purchase_count']
            
            if days <= 30 and frequency <= 2:
                lifecycle_stages['新客户'] += 1
            elif r >= 2 and f == 2 and m >= 1:
                lifecycle_stages['成长客户'] += 1
            elif r == 3 and f >= 2 and m >= 2:
                lifecycle_stages['活跃客户'] += 1
            elif r >= 2 and f == 3 and m == 3:
                lifecycle_stages['忠诚客户'] += 1
            elif r == 1 and f >= 2 and days <= 90:
                lifecycle_stages['沉睡客户'] += 1
            elif r == 1 and f >= 2 and days > 90 and days <= 180:
                lifecycle_stages['流失风险'] += 1
            elif days > 180:
                lifecycle_stages['已流失'] += 1
            else:
                # 默认归为成长客户
                lifecycle_stages['成长客户'] += 1
                
        # 转换为图表所需格式
        result = []
        colors = {
            '新客户': '#00ffb3',
            '成长客户': '#3a36db',
            '活跃客户': '#00c9ff',
            '忠诚客户': '#6236ff',
            '沉睡客户': '#ffb800',
            '流失风险': '#ff9500',
            '已流失': '#ff6b6b'
        }
        
        for stage, count in lifecycle_stages.items():
            result.append({
                'name': stage,
                'value': count,
                'itemStyle': {'color': colors.get(stage, '#cccccc')}
            })
            
        logger.info(f"客户生命周期API返回 {len(result)} 个生命周期阶段")
        return Response(result)
    except Exception as e:
        logger.error(f"客户生命周期API出错: {str(e)}")
        return Response([])

@api_view(['GET'])
# @permission_classes([IsAuthenticated])  # 暂时注释掉权限检查以排除认证问题
def get_monetary_distribution(request):
    """获取消费金额分布数据"""
    logger = logging.getLogger(__name__)
    logger.info("消费金额分布API被调用")
    
    try:
        # 获取所有客户RFM数据
        customers_data = get_customers_rfm_data()
        
        # 定义金额区间
        ranges = [
            {"min": 0, "max": 1000, "label": "0-1000"},
            {"min": 1000, "max": 5000, "label": "1000-5000"},
            {"min": 5000, "max": 10000, "label": "5000-10000"},
            {"min": 10000, "max": 50000, "label": "10000-50000"},
            {"min": 50000, "max": float('inf'), "label": "50000+"}
        ]
        
        # 初始化区间计数
        distribution = {r["label"]: 0 for r in ranges}
        
        # 根据客户消费金额分组
        for customer in customers_data:
            amount = customer["total_amount"]
            for r in ranges:
                if r["min"] <= amount < r["max"]:
                    distribution[r["label"]] += 1
                    break
        
        # 转换为图表所需格式
        result = []
        colors = ['#00ffb3', '#3a36db', '#00c9ff', '#6236ff', '#ff9500']
        
        for i, (label, count) in enumerate(distribution.items()):
            result.append({
                "name": label,
                "value": count,
                "itemStyle": {"color": colors[i % len(colors)]}
            })
            
        logger.info(f"消费金额分布API返回 {len(result)} 个金额区间")
        return Response(result)
    except Exception as e:
        logger.error(f"消费金额分布API出错: {str(e)}")
        return Response([]) 